const express = require('express');
const router = express.Router();
const con = require("../db")
const con2 = require("../db/mysql")
const md5 = require("md5")
/* GET home page. */

// 分页查询
router.get('/', async (req, res) => {
  const { page, limit, rid, username } = req.query;
  let where = "";
  if (rid) {
    where += `AND manager.rid=${rid}`
  }
  if (username) {
    where += `AND username LIKE '%${username}%' `
  }
  const result = await con2.query(`SELECT manager.id,manager.username,role.rolename FROM manager,role WHERE manager.rid=role.id ${where} ORDER BY id DESC LIMIT ${limit} OFFSET ${(page - 1) * limit};
    SELECT COUNT(*) AS total FROM manager WHERE 1 ${where}`);
  res.json({
    msg: "查询成功",
    code: 200,
    data: result[0],
    total: result[1][0].total,
  })
});
router.post('/login', (req, res) => {
  const { username, password } = req.body;
  con.query(`SELECT * FROM manager WHERE username=? AND password=?`, [username, md5(password)], (err, result) => {
    if (err) throw err;
    if (result.length > 0) {
      res.json({
        msg: "登陆成功",
        code: 200,
        id: result[0].id,
        rid:result[0].rid,
      })
    }
    else {
      res.json({
        msg: "登陆失败",
        code: 500
      })
    }
  })

});
router.put('/password', (req, res) => {
  const { id, password, newpassword } = req.body;
  con.query(`SELECT * FROM manager WHERE id=? AND password=?`, [id, md5(password)], (err, result) => {
    if (err) throw err;
    if (result.length > 0) {
      con.query(`UPDATE manager SET password=? WHERE id=?`, [md5(newpassword), id], (err, result) => {
        if (err) throw err;
        if (result.affectedRows === 1) {
          res.json({
            msg: "更新成功",
            code: 200
          })
        } else {
          res.json({
            msg: "更新失败",
            code: 500
          })
        }
      })
    } else {
      res.json({
        msg: "原始密码错误",
        code: 500
      })
    }
  })
});
router.delete('/:id', (req, res) => {
  const { id } = req.params;
  con.query(`DELETE FROM manager WHERE id=?`, id, (err, result) => {
    if (err) throw err;

    if (result.affectedRows === 1) {
      res.json({
        msg: "删除成功",
        code: 200
      });
    } else {
      res.json({
        msg: "删除失败",
        code: 500
      });
    }
  });
});
router.get('/findbyrid', (req, res, next) => {
  const { rid } = req.query;
  con.query(`SELECT * FROM manager WHERE rid=?`, rid, (err, result) => {
    if (err) throw err;
    if (result.length) {
      res.json({
        msg: "当前角色有用户",
        code: 201
      })
    } else {
      res.json({
        msg: "当前角色无用户",
        code: 202
      })
    }
  })
});

router.post('/', async (req, res) => {
  const { body } = req;
  const result = await con2.query(`
     INSERT INTO manager(username,password,rid) VALUES (?,?,?)`, [body.username, md5("123456"), body.rid]);
  if (result.affectedRows === 1) {
    res.json({
      msg: "添加成功",
      code: 200
    });
  } else {
    res.json({
      msg: "添加失败",
      code: 500
    });
  }
});
router.put('/:id', (req, res) => {
  const { id } = req.params;
  const { username, rolename } = req.body;
  con.query(`SELECT * FROM manager WHERE username=? `, username, (err, result) => {
    if (err) throw err;
    if (result.length > 0) {
      res.json({
        msg: "用户名重复",
        code: 500
      })
    }else{
      con.query(`UPDATE manager SET username=?,rid=? WHERE id=?`, [username, rolename,id], (err, result) => {
        if (err) throw err;
        if (result.affectedRows === 1) {
          res.json({
            msg: "更新成功",
            code: 200
          })
        } else {
          res.json({
            msg: "更新失败",
            code: 500
          })
        }
      })
    }
  })
});
module.exports = router;
